package com.ywjh.inter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.ywjh.inter.entity.BloodPressureEntity;

public class BloodPressureDAO {

	public boolean insert(Connection conn, BloodPressureEntity bloodPressureEntity) throws SQLException {
		boolean b = false;
		PreparedStatement ps = conn.prepareStatement(
				"INSERT INTO blood_pressure (user_id, hi, low, pulse, add_time,check_time, status,memo,data_source) VALUES (?,?,?,?,?,?,?,?,?)");
		ps.setInt(1, bloodPressureEntity.getUser_id());
		ps.setFloat(2, bloodPressureEntity.getHi());
		ps.setFloat(3, bloodPressureEntity.getLow());
		ps.setInt(4, bloodPressureEntity.getPulse());
		ps.setTimestamp(5, bloodPressureEntity.getAdd_time());
		ps.setTimestamp(6, bloodPressureEntity.getCheck_time());
		ps.setInt(7, bloodPressureEntity.getStatus());
		ps.setString(8, bloodPressureEntity.getMemo());
		ps.setString(9, bloodPressureEntity.getData_source());
		if(ps.executeUpdate()>0) {
			b=true;
		}
		return b;
	}

	public boolean update(Connection conn, BloodPressureEntity bloodPressureEntity) throws SQLException {
		boolean b = false;
		PreparedStatement ps = conn.prepareStatement(
				"update blood_pressure set user_id=?, hi=?, low=?, pulse=?, add_time=?, status=?,check_time=?,memo=?,data_source=? where id = ?");
		ps.setInt(1, bloodPressureEntity.getUser_id());
		ps.setFloat(2, bloodPressureEntity.getHi());
		ps.setFloat(3, bloodPressureEntity.getLow());
		ps.setInt(4, bloodPressureEntity.getPulse());
		ps.setTimestamp(5, bloodPressureEntity.getAdd_time());
		ps.setInt(6, bloodPressureEntity.getStatus());
		ps.setTimestamp(7, bloodPressureEntity.getCheck_time());
		ps.setString(8, bloodPressureEntity.getMemo());
		ps.setString(9, bloodPressureEntity.getData_source());
		ps.setInt(10, bloodPressureEntity.getId());
		if(ps.executeUpdate()>0) {
			b=true;
		}
		return b;
	}

	public BloodPressureEntity findById(Connection conn,int id) throws SQLException {
		BloodPressureEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from blood_pressure where id = ? ");
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}

	private BloodPressureEntity fill(ResultSet rs) throws SQLException {
		BloodPressureEntity e=new BloodPressureEntity();
		e.setId(rs.getInt("id"));
		e.setHi(rs.getFloat("hi"));
		e.setAdd_time(rs.getTimestamp("add_time"));
		e.setLow(rs.getFloat("low"));
		e.setPulse(rs.getInt("pulse"));
		e.setStatus(rs.getInt("status"));
		e.setUser_id(rs.getInt("user_id"));
		e.setCheck_time(rs.getTimestamp("check_time"));
		e.setMemo(rs.getString("memo"));
		e.setData_source(rs.getString("data_source"));
		return e;
	}

	public List<BloodPressureEntity> query(Connection conn, int userId, String time) throws SQLException {
		List<BloodPressureEntity> list=new LinkedList<BloodPressureEntity>();
		PreparedStatement ps=conn.prepareStatement("select * from blood_pressure where user_id=? and date_format(check_time,'%y%m%d') = date_format(?,'%y%m%d') and status=1");
		ps.setInt(1, userId);ps.setString(2, time);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodPressureEntity> query(Connection conn, int userId, String startTime,String endTime) throws SQLException {
		List<BloodPressureEntity> list=new LinkedList<>();
		PreparedStatement ps=conn.prepareStatement("select * from blood_pressure where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id=? and status=1");
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		ps.setInt(3, userId);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodPressureEntity> query(Connection conn, String users, String startTime, String endTime) throws SQLException {
		List<BloodPressureEntity> list=new LinkedList<>();
		if(users==null||"".equals(users)) {
			return list;
		}
		PreparedStatement ps=conn.prepareStatement("select * from blood_pressure where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id in ("+users+") and status=1");
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodPressureEntity> query(Connection conn, String users) throws SQLException {
		List<BloodPressureEntity> list=new LinkedList<>();
		if(users==null||"".equals(users)) {
			return list;
		}
		PreparedStatement ps=conn.prepareStatement("select * from blood_pressure where  user_id in ("+users+") and status=1");
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}
}
